﻿Imports System.Xml
Imports System.Reflection
Imports Mysoft.Map.Workflow.Types
Imports Mysoft.Map.Workflow.BusinessDomain
Imports Mysoft.Map.Data
Imports System.Web


Public Class ProceedPlanSP

    Inherits BusinessDomainBase

    Private _businessGUID As String     '业务GUID
    Private _xml As XmlDocument = New XmlDocument

    Public Sub New()
        MyBase.New("/PubProject/workflow/qmys/ProceedPlanSP.xml")
        _xml.LoadXml(MyBase.XML)
    End Sub

    '属性：业务GUID
    Public Overrides Property BusinessGUID() As String
        Get
            Return _businessGUID
        End Get
        Set(ByVal Value As String)
            _businessGUID = Value
        End Set
    End Property

    '属性：XML
    '最终XML格式：

    Public Overrides Property XML() As String
        Get
            Return _xml.OuterXml
        End Get
        Set(ByVal Value As String)
            _xml.LoadXml(Value)
        End Set
    End Property

    Public Overrides Function Initiate() As String
        Dim strAll As String = MyDB.GetDataItemString("SELECT cast(Year as nvarchar(10)) + '|' + cast(ObjectGUID as nvarchar(50)) + '|' + cast(YearPlanGUID as nvarchar(50)) FROM ys_YearPlan2Object WHERE YearPlan2ObjectGUID = '" & Me._businessGUID & "'")
        Dim strYear As String = strAll.Split("|")(0)
        Dim strDeptGUID As String = strAll.Split("|")(1)
        Dim strYearPlanGUID As String = strAll.Split("|")(2)
        Dim strCostGUID As String = MyDB.GetDataItemString("select * from ys_DeptCost where ys_DeptCost.year=" & strYear & " and ys_DeptCost.costlevel=1")
        Dim strSQL As String

        'Dim BackYearItemState As String = MyDB.GetDataItemString("SELECT 1 FROM ys_YearPlan2Object WHERE (ItemState='已审核' OR ItemState='公司已审核' OR ItemState='集团已审核') AND ObjectGUID='" & strDeptGUID & "' AND Year > " & strYear & "")

        '校验:当前预算对应年度之后年度存在已审核预算，不允许发起审批！
        'If BackYearItemState = "1" Then
        '    Return "<xml result=""false"" errormessage=""当前预算对应年度之后年度存在已审核预算，不允许发起审批！""></xml>"
        'End If

        Dim sItemState As String = MyDB.GetDataItemString("SELECT ItemState FROM ys_YearPlan2Object WHERE Year = " & strYear & " AND ObjectGUID='" & strDeptGUID & "' AND YearPlanGUID='" & strYearPlanGUID & "'")

        '校验:公司年度预算已上报，不允许发起审批！
        If sItemState = "审核中" Then
            Return "<xml result=""false"" errormessage=""公司年度预算已上报，不允许发起审批！""></xml>"
        End If

        '校验:公司年度预算已审核，不允许发起审批！
        If sItemState = "已审核" Then
            Return "<xml result=""false"" errormessage=""公司年度预算已审核，不允许发起审批！""></xml>"
        End If

        Try
            Dim myConnection As SqlClient.SqlConnection = New SqlClient.SqlConnection(MyDB.GetSqlConnectionString())
            Dim myCommand As SqlClient.SqlCommand = New SqlClient.SqlCommand("usp_Ys_ReportDeptYearPlan", myConnection)

            myCommand.CommandType = CommandType.StoredProcedure
            Dim sqlParams As SqlClient.SqlParameterCollection = myCommand.Parameters

            sqlParams.Add(New SqlClient.SqlParameter("@strDeptGUID", SqlDbType.VarChar, 40))
            sqlParams.Add(New SqlClient.SqlParameter("@strYearPlanGUID", SqlDbType.VarChar, 40))
            sqlParams.Add(New SqlClient.SqlParameter("@intYear", SqlDbType.Int))

            '参数赋值
            sqlParams("@strDeptGUID").Value = strDeptGUID
            sqlParams("@strYearPlanGUID").Value = strYearPlanGUID
            sqlParams("@intYear").Value = strYear

            ''执行存储过程
            Try
                If (myConnection.State = ConnectionState.Closed) Then
                    myConnection.Open()
                End If
                myCommand.ExecuteNonQuery()
            Catch exc As Exception
            Finally
                myConnection.Close()
            End Try
        Catch ex As Exception
            Return "<xml result=""false"" errormessage=""上报失败，请稍后再试！""></xml>"
        End Try

        strSQL = "UPDATE ys_YearPlan2Object SET ItemState='审核中',ReportedByGUID='" & HttpContext.Current.Session("UserGUID") & "',ReportedBy='" & HttpContext.Current.Session("UserName") & "',ReportDate=GetDate()" & _
                 ",PlanAmountYear=(select sum(PlanAmount1+PlanAmount2+PlanAmount3+PlanAmount4+PlanAmount5+PlanAmount6+PlanAmount7+PlanAmount8+PlanAmount9+PlanAmount10+PlanAmount11+PlanAmount12) from ys_YearPlanDeptExpend where costguid='" & strCostGUID & "' and deptguid='" & strDeptGUID & "' and YearNum=" & strYear & " and YearPlanGUID='" & strYearPlanGUID & "')" & _
                 ",PlanAmount1=(select PlanAmount1 from ys_YearPlanDeptExpend where costguid='" & strCostGUID & "' and deptguid='" & strDeptGUID & "' and YearNum=" & strYear & " and YearPlanGUID='" & strYearPlanGUID & "')" & _
                 ",PlanAmount2=(select PlanAmount2 from ys_YearPlanDeptExpend where costguid='" & strCostGUID & "' and deptguid='" & strDeptGUID & "' and YearNum=" & strYear & " and YearPlanGUID='" & strYearPlanGUID & "')" & _
                 ",PlanAmount3=(select PlanAmount3 from ys_YearPlanDeptExpend where costguid='" & strCostGUID & "' and deptguid='" & strDeptGUID & "' and YearNum=" & strYear & " and YearPlanGUID='" & strYearPlanGUID & "')" & _
                 ",PlanAmount4=(select PlanAmount4 from ys_YearPlanDeptExpend where costguid='" & strCostGUID & "' and deptguid='" & strDeptGUID & "' and YearNum=" & strYear & " and YearPlanGUID='" & strYearPlanGUID & "')" & _
                 ",PlanAmount5=(select PlanAmount5 from ys_YearPlanDeptExpend where costguid='" & strCostGUID & "' and deptguid='" & strDeptGUID & "' and YearNum=" & strYear & " and YearPlanGUID='" & strYearPlanGUID & "')" & _
                 ",PlanAmount6=(select PlanAmount6 from ys_YearPlanDeptExpend where costguid='" & strCostGUID & "' and deptguid='" & strDeptGUID & "' and YearNum=" & strYear & " and YearPlanGUID='" & strYearPlanGUID & "')" & _
                 ",PlanAmount7=(select PlanAmount7 from ys_YearPlanDeptExpend where costguid='" & strCostGUID & "' and deptguid='" & strDeptGUID & "' and YearNum=" & strYear & " and YearPlanGUID='" & strYearPlanGUID & "')" & _
                 ",PlanAmount8=(select PlanAmount8 from ys_YearPlanDeptExpend where costguid='" & strCostGUID & "' and deptguid='" & strDeptGUID & "' and YearNum=" & strYear & " and YearPlanGUID='" & strYearPlanGUID & "')" & _
                 ",PlanAmount9=(select PlanAmount9 from ys_YearPlanDeptExpend where costguid='" & strCostGUID & "' and deptguid='" & strDeptGUID & "' and YearNum=" & strYear & " and YearPlanGUID='" & strYearPlanGUID & "')" & _
                 ",PlanAmount10=(select PlanAmount10 from ys_YearPlanDeptExpend where costguid='" & strCostGUID & "' and deptguid='" & strDeptGUID & "' and YearNum=" & strYear & " and YearPlanGUID='" & strYearPlanGUID & "')" & _
                 ",PlanAmount11=(select PlanAmount11 from ys_YearPlanDeptExpend where costguid='" & strCostGUID & "' and deptguid='" & strDeptGUID & "' and YearNum=" & strYear & " and YearPlanGUID='" & strYearPlanGUID & "')" & _
                 ",PlanAmount12=(select PlanAmount12 from ys_YearPlanDeptExpend where costguid='" & strCostGUID & "' and deptguid='" & strDeptGUID & "' and YearNum=" & strYear & " and YearPlanGUID='" & strYearPlanGUID & "')" & _
                 " WHERE YearPlan2ObjectGUID = '" & Me._businessGUID & "'"
        '提交更新语句
        If MyDB.ExecSQL(strSQL) < 0 Then
            Return "<xml result=""false"" errormessage=""上报失败，请稍后再试！""></xml>"
        End If

        '返回执行是否成功的信息
        Return "<xml result=""true"" errormessage=""""></xml>"
    End Function
    '功能：取业务数据，填充XML
    Public Overrides Function LoadData() As Integer
        Dim strSQL As String
        Dim dtTemp As DataTable

        Dim strYear As String = MyDB.GetDataItemString("SELECT Year FROM ys_YearPlan2Object WHERE YearPlan2ObjectGUID = '" & Me._businessGUID & "'")
        Dim strDeptGUID As String = MyDB.GetDataItemString("SELECT ObjectGUID FROM ys_YearPlan2Object WHERE YearPlan2ObjectGUID = '" & Me._businessGUID & "'")
        Dim strCostGUID As String = MyDB.GetDataItemString("SELECT deptcostguid FROM ys_DeptCost WHERE ys_DeptCost.year=" & strYear & " AND ys_DeptCost.costlevel=1")

        '主记录    
        strSQL = "SELECT YearPlan2ObjectGUID, ObjectGUID,itemstate as State,BUName, Year,Convert(varchar(10),GetDate(),120) GetDay,(select PlanAmount1+PlanAmount2+PlanAmount3+PlanAmount4+PlanAmount5+PlanAmount6+PlanAmount7+PlanAmount8+PlanAmount9+PlanAmount10+PlanAmount11+PlanAmount12+Adjustamount1+Adjustamount2+Adjustamount3+Adjustamount4+Adjustamount5+Adjustamount6+Adjustamount7+Adjustamount8+Adjustamount9+Adjustamount10+Adjustamount11+Adjustamount12 from ys_YearPlanDept2Cost where year=" & strYear & " and deptguid='" & strDeptGUID & "' and costguid='" & strCostGUID & "') PlanAmountYear,ReportedBy, ReportDate " & _
                 " FROM ys_YearPlan2Object Left Join vys_myBusinessUnit On ys_YearPlan2Object.ObjectGUID = vys_myBusinessUnit.BUGUID " & _
                 " WHERE YearPlan2ObjectGUID='" & Me._businessGUID & "'"

        dtTemp = MyDB.GetDataTable(strSQL)
        If dtTemp.Rows.Count > 0 Then
            '根节点业务GUID属性
            _xml.SelectSingleNode("/BusinessType").Attributes.GetNamedItem("BusinessGUID").Value = Me._businessGUID
            _xml.SelectSingleNode("/BusinessType/Item/Domain[@name='年度预算计划2部门GUID']").InnerText = dtTemp.Rows(0).Item("YearPlan2ObjectGUID").ToString
            _xml.SelectSingleNode("/BusinessType/Item/Domain[@name='部门GUID']").InnerText = dtTemp.Rows(0).Item("ObjectGUID").ToString
            _xml.SelectSingleNode("/BusinessType/Item/Domain[@name='申请部门']").InnerText = dtTemp.Rows(0).Item("BUName").ToString
            _xml.SelectSingleNode("/BusinessType/Item/Domain[@name='年度']").InnerText = dtTemp.Rows(0).Item("Year").ToString
            _xml.SelectSingleNode("/BusinessType/Item/Domain[@name='上报金额']").InnerText = Format(dtTemp.Rows(0).Item("planamountyear"), "#,##0.00")
            _xml.SelectSingleNode("/BusinessType/Item/Domain[@name='上报人名称']").InnerText = HttpContext.Current.Session("UserName").ToString
            _xml.SelectSingleNode("/BusinessType/Item/Domain[@name='上报日期']").InnerText = dtTemp.Rows(0).Item("GetDay").ToString
            _xml.SelectSingleNode("/BusinessType/Item/Domain[@name='状态']").InnerText = dtTemp.Rows(0).Item("State").ToString
        End If

        Return 0
    End Function

    '功能：根据XML进行数据归档
    Public Overrides Function SaveData() As String
        Dim MyPublicMng As New PublicMNG
        '获取审核人、审核日期的审核抬头信息
        Dim strApproveSeparator As String = MyPublicMng.GetApproveSeparator("审批通过")
        Dim strSQL As String
        'strSQL = "SELECT 1 FROM myBusinessUnit WHERE level=0 and buguid in(select buguid from ys_YearPlan2Object where yearplan2objectguid='" & Me._businessGUID & "')"

        '判断是否是集团
        'If MyDB.GetDataItemInt(strSQL) = 1 Then
        '    strSQL = "UPDATE ys_YearPlan2Object SET ItemState='集团已审核',ReItemState='集团已审核'" & _
        '    ",ApprovedBy='" & HttpContext.Current.Session("UserName") & "'" & _
        '    ",ApproveDate=Convert(varchar(10),GetDate(),120)" & _
        '    ",ApproveRemarks='" & strApproveSeparator & Chr(13) & Chr(10) & Chr(13) & Chr(10) & Chr(13) & Chr(10) & "' + Convert(varchar(6000), IsNull(ApproveRemarks, '')) " & _
        '    " WHERE YearPlan2ObjectGUID='" & Me._businessGUID & "';"
        'Else
        strSQL = "UPDATE ys_YearPlan2Object SET ItemState='已审核'" & _
                    ",ApprovedBy='" & HttpContext.Current.Session("UserName") & "'" & _
                    ",ApproveDate=GetDate()" & _
                    ",ApproveRemarks='" & strApproveSeparator & Chr(13) & Chr(10) & "    工作流审批通过" & Chr(13) & Chr(10) & Chr(13) & Chr(10) & "' + Convert(varchar(6000), IsNull(ApproveRemarks, '')) " & _
                    " WHERE YearPlan2ObjectGUID='" & Me._businessGUID & "';"
        'End If



        If MyDB.ExecSQL(strSQL) < 0 Then
            Return "<xml result=""false"" errormessage=""归档失败，请稍后再试！""></xml>"
        End If

        Return "<xml result=""true"" errormessage=""""></xml>"
    End Function

    '功能：不通过处理
    Public Overrides Function SaveData_NoPass() As String
        Dim MyPublicMng As New PublicMNG
        '获取审核人、审核日期的审核抬头信息
        Dim strApproveSeparator As String = MyPublicMng.GetApproveSeparator("审批不通过")

        Dim strAll As String = MyDB.GetDataItemString("SELECT cast(Year as nvarchar(10)) + '|' + cast(ObjectGUID as nvarchar(50)) + '|' + cast(YearPlanGUID as nvarchar(50)) FROM ys_YearPlan2Object WHERE YearPlan2ObjectGUID = '" & Me._businessGUID & "'")
        Dim strYear As String = strAll.Split("|")(0)
        Dim strDeptGUID As String = strAll.Split("|")(1)
        Dim strYearPlanGUID As String = strAll.Split("|")(2)
        MyDB.ExecuteSQL("DELETE ys_YearPlanDeptExpend WHERE year=" & strYear & " and deptguid='" & strDeptGUID & "' and YearPlanguid='" & strYearPlanGUID & "'")

        Dim strSQL As String
        strSQL = "UPDATE ys_YearPlan2Object SET ItemState='未审核',ReportedByGUID=NULL,ReportedBy=NULL,ReportDate=NULL" & _
               ",ApproveRemarks='" & strApproveSeparator & Chr(13) & Chr(10) & "    工作流审批不通过" & Chr(13) & Chr(10) & Chr(13) & Chr(10) & "' + Convert(varchar(6000), IsNull(ApproveRemarks, '')) " & _
               ",PlanAmountYear=0.00" & _
               ",PlanAmount1=0.00" & _
               ",PlanAmount2=0.00" & _
               ",PlanAmount3=0.00" & _
               ",PlanAmount4=0.00" & _
               ",PlanAmount5=0.00" & _
               ",PlanAmount6=0.00" & _
               ",PlanAmount7=0.00" & _
               ",PlanAmount8=0.00" & _
               ",PlanAmount9=0.00" & _
               ",PlanAmount10=0.00" & _
               ",PlanAmount11=0.00" & _
               ",PlanAmount12=0.00" & _
               " WHERE YearPlan2ObjectGUID = '" & Me._businessGUID & "'"

        If MyDB.ExecSQL(strSQL) < 0 Then
            Return "<xml result=""false"" errormessage=""操作失败，请稍后再试！""></xml>"
        End If

        Return "<xml result=""true"" errormessage=""""></xml>"

    End Function
    '功能：作废
    Public Overrides Function SaveData_Cancel() As String
        Dim MyPublicMng As New PublicMNG
        '获取审核人、审核日期的审核抬头信息
        Dim strApproveSeparator As String = MyPublicMng.GetApproveSeparator("审批作废")

        Dim strAll As String = MyDB.GetDataItemString("SELECT cast(Year as nvarchar(10)) + '|' + cast(ObjectGUID as nvarchar(50)) + '|' + cast(YearPlanGUID as nvarchar(50)) FROM ys_YearPlan2Object WHERE YearPlan2ObjectGUID = '" & Me._businessGUID & "'")
        Dim strYear As String = strAll.Split("|")(0)
        Dim strDeptGUID As String = strAll.Split("|")(1)
        Dim strYearPlanGUID As String = strAll.Split("|")(2)
        MyDB.ExecuteSQL("DELETE ys_YearPlanDeptExpend WHERE year=" & strYear & " and deptguid='" & strDeptGUID & "' and YearPlanguid='" & strYearPlanGUID & "'")

        Dim strSQL As String
        strSQL = "UPDATE ys_YearPlan2Object SET ItemState='未审核',ReportedByGUID=NULL,ReportedBy=NULL,ReportDate=NULL" & _
              ",ApproveRemarks='" & strApproveSeparator & Chr(13) & Chr(10) & "    工作流审批作废" & Chr(13) & Chr(10) & Chr(13) & Chr(10) & "' + Convert(varchar(6000), IsNull(ApproveRemarks, '')) " & _
              ",PlanAmountYear=0.00" & _
              ",PlanAmount1=0.00" & _
              ",PlanAmount2=0.00" & _
              ",PlanAmount3=0.00" & _
              ",PlanAmount4=0.00" & _
              ",PlanAmount5=0.00" & _
              ",PlanAmount6=0.00" & _
              ",PlanAmount7=0.00" & _
              ",PlanAmount8=0.00" & _
              ",PlanAmount9=0.00" & _
              ",PlanAmount10=0.00" & _
              ",PlanAmount11=0.00" & _
              ",PlanAmount12=0.00" & _
              " WHERE YearPlan2ObjectGUID = '" & Me._businessGUID & "'"

        If MyDB.ExecSQL(strSQL) < 0 Then
            Return "<xml result=""false"" errormessage=""操作失败，请稍后再试！""></xml>"
        End If

        Return "<xml result=""true"" errormessage=""""></xml>"
    End Function

    '功能：审批通过后再驳回数据处理
    Public Function SaveData_RollBack() As String
        Dim MyPublicMng As New PublicMNG
        '获取审核人、审核日期的审核抬头信息
        Dim strApproveSeparator As String = MyPublicMng.GetApproveSeparator("审批驳回")

        Dim strAll As String = MyDB.GetDataItemString("SELECT cast(Year as nvarchar(10)) + '|' + cast(ObjectGUID as nvarchar(50)) + '|' + cast(YearPlanGUID as nvarchar(50)) FROM ys_YearPlan2Object WHERE YearPlan2ObjectGUID = '" & Me._businessGUID & "'")
        Dim strYear As String = strAll.Split("|")(0)
        Dim strDeptGUID As String = strAll.Split("|")(1)
        Dim strYearPlanGUID As String = strAll.Split("|")(2)
        MyDB.ExecuteSQL("DELETE ys_YearPlanDeptExpend WHERE year=" & strYear & " and deptguid='" & strDeptGUID & "' and YearPlanguid='" & strYearPlanGUID & "'")

        Dim strSQL As String
        strSQL = "UPDATE ys_YearPlan2Object SET ItemState='未审核',ReportedByGUID=NULL,ReportedBy=NULL,ReportDate=NULL" & _
               ",ApproveRemarks='" & strApproveSeparator & Chr(13) & Chr(10) & "    工作流审批驳回" & Chr(13) & Chr(10) & Chr(13) & Chr(10) & "' + Convert(varchar(6000), IsNull(ApproveRemarks, '')) " & _
               ",PlanAmountYear=0.00" & _
               ",PlanAmount1=0.00" & _
               ",PlanAmount2=0.00" & _
               ",PlanAmount3=0.00" & _
               ",PlanAmount4=0.00" & _
               ",PlanAmount5=0.00" & _
               ",PlanAmount6=0.00" & _
               ",PlanAmount7=0.00" & _
               ",PlanAmount8=0.00" & _
               ",PlanAmount9=0.00" & _
               ",PlanAmount10=0.00" & _
               ",PlanAmount11=0.00" & _
               ",PlanAmount12=0.00" & _
               " WHERE YearPlan2ObjectGUID = '" & Me._businessGUID & "'"

        If MyDB.ExecSQL(strSQL) < 0 Then
            Return "<xml result=""false"" errormessage=""操作失败，请稍后再试！""></xml>"
        End If

        Return "<xml result=""true"" errormessage=""""></xml>"

    End Function

    '功能：发起撤回
    Public Overrides Function InitiateWithDrawn() As String
        Dim MyPublicMng As New PublicMNG
        '获取审核人、审核日期的审核抬头信息
        Dim strApproveSeparator As String = MyPublicMng.GetApproveSeparator("审批不通过")

        Dim strAll As String = MyDB.GetDataItemString("SELECT cast(Year as nvarchar(10)) + '|' + cast(ObjectGUID as nvarchar(50)) + '|' + cast(YearPlanGUID as nvarchar(50)) FROM ys_YearPlan2Object WHERE YearPlan2ObjectGUID = '" & Me._businessGUID & "'")
        Dim strYear As String = strAll.Split("|")(0)
        Dim strDeptGUID As String = strAll.Split("|")(1)
        Dim strYearPlanGUID As String = strAll.Split("|")(2)
        MyDB.ExecuteSQL("DELETE ys_YearPlanDeptExpend WHERE year=" & strYear & " and deptguid='" & strDeptGUID & "' and YearPlanguid='" & strYearPlanGUID & "'")

        Dim strSQL As String
        strSQL = "UPDATE ys_YearPlan2Object SET ItemState='未审核',ReportedByGUID=NULL,ReportedBy=NULL,ReportDate=NULL" & _
               ",ApproveRemarks='" & strApproveSeparator & Chr(13) & Chr(10) & "    工作流审批不通过" & Chr(13) & Chr(10) & Chr(13) & Chr(10) & "' + Convert(varchar(6000), IsNull(ApproveRemarks, '')) " & _
               ",PlanAmountYear=0.00" & _
               ",PlanAmount1=0.00" & _
               ",PlanAmount2=0.00" & _
               ",PlanAmount3=0.00" & _
               ",PlanAmount4=0.00" & _
               ",PlanAmount5=0.00" & _
               ",PlanAmount6=0.00" & _
               ",PlanAmount7=0.00" & _
               ",PlanAmount8=0.00" & _
               ",PlanAmount9=0.00" & _
               ",PlanAmount10=0.00" & _
               ",PlanAmount11=0.00" & _
               ",PlanAmount12=0.00" & _
               " WHERE YearPlan2ObjectGUID = '" & Me._businessGUID & "'"

        If MyDB.ExecSQL(strSQL) < 0 Then
            Return "<xml result=""false"" errormessage=""操作失败，请稍后再试！""></xml>"
        End If

        Return "<xml result=""true"" errormessage=""""></xml>"
    End Function
End Class
